﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace lab2
{
    public partial class passengers : Form
    {
        public passengers()
        {
            InitializeComponent();
        }

        private void passengers_Load(object sender, EventArgs e)
        {
            this.changeBil();

        }

        private void changeBil()
        {
            int id;
            if (dataGridView1.CurrentRow != null )
            {
                DataGridViewRow currentRow = dataGridView1.CurrentRow;
                id = Convert.ToInt32(currentRow.Cells[0].Value);
            }
            else
            {
                id = 1;
            }

            cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = D:\\studying\\6sem\\БД\\Student\\passengers.mdb");
            cn.Open();
            cm = new OleDbCommand("SELECT Билеты.seat, Рейсы.to, Рейсы.flight_num FROM Рейсы INNER JOIN (Пассажиры INNER JOIN Билеты ON Пассажиры.id_passenger = Билеты.id_passenger) ON Рейсы.id_flight = Билеты.id_flight WHERE Билеты.id_passenger = " + id, cn);
            da = new OleDbDataAdapter();
            da.SelectCommand = cm;
            dt = new DataTable();
            da.Fill(dt);
            dataGridView2.DataSource = dt;
            cn.Close();
            cm.Dispose();
            da.Dispose();
            dt.Dispose();
        }

        OleDbConnection cn;
        OleDbCommand cm;
        OleDbDataAdapter da;
        DataTable dt;
        public void testFunction()
        {
            cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = D:\\studying\\6sem\\БД\\Student\\passengers.mdb");
            cn.Open();
            cm = new OleDbCommand("SELECT пассажиры.id_passenger as номер, пассажиры.first_name as Имя, пассажиры.middle_name as отчество, пассажиры.second_name as фамилия, пассажиры.passport_num as паспорт FROM пассажиры", cn);
            da = new OleDbDataAdapter();
            da.SelectCommand = cm;
            dt = new DataTable();
            da.Fill(dt);
            dataGridView1.DataSource = dt;

            cn.Close();
            cm.Dispose();
            da.Dispose();
            dt.Dispose();

        }

        private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = D:\\studying\\6sem\\БД\\Student\\passengers.mdb");
            cn.Open();
            string name = "";
            switch (dataGridView1.Columns[e.ColumnIndex].Name)
            {
                case "Имя":
                    name = "first_name";
                    break;
                case "отчество":
                    name = "middle_name";
                    break;
                case "фамилия":
                    name = "second_name";
                    break;
                case "паспорт":
                    name = "passport_num";
                    break;


            }
            string test = "UPDATE пассажиры SET `" + name + "`='" + dataGridView1[e.ColumnIndex, e.RowIndex].Value.ToString() + "' WHERE пассажиры.id_passenger=" + dataGridView1[0, e.RowIndex].Value.ToString();
            cm = new OleDbCommand(test, cn);
            cm.ExecuteNonQuery();
            cn.Close();
            cm.Dispose();
            da.Dispose();
            dt.Dispose();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            int id;
            if (dataGridView1.CurrentRow != null)
            {
                DataGridViewRow currentRow = dataGridView1.CurrentRow;
                id = Convert.ToInt32(currentRow.Cells[0].Value);
            }
            else
            {
                id = 0;
            }
            cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = D:\\studying\\6sem\\БД\\Student\\passengers.mdb");
            cn.Open();
            string test = "DELETE * FROM Билеты WHERE Билеты.id_passenger = " + id;
            cm = new OleDbCommand(test, cn);
            cm.ExecuteNonQuery();
            cm.Dispose();
            test = "DELETE * FROM Пассажиры WHERE Пассажиры.id_passenger = " + id;
            cm = new OleDbCommand(test, cn);
            cm.ExecuteNonQuery();
            cn.Close();
            cm.Dispose();
            da.Dispose();
            dt.Dispose();
            dataGridView1.Rows.RemoveAt(dataGridView1.CurrentRow.Index);
        }

        private void passengers_Load(object sender, KeyEventArgs e)
        {

            this.changeBil();
        }

        private void dataGridView1_KeyUp(object sender, KeyEventArgs e)
        {

            this.changeBil();
        }

        private void dataGridView1_KeyPress(object sender, KeyPressEventArgs e)
        {

            this.changeBil();
        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {

            this.changeBil();
        }
    }
}
